use std::{panic, result};
use log::{error, info};
use regex::Regex;
use umya_spreadsheet::{Cell, CellRawValue, Spreadsheet, Worksheet};
use umya_spreadsheet::reader::xlsx::XlsxError;
use crate::config_util::{get_config, get_excel_path_mem};
use crate::get_excel_path;
use crate::model::{IdNumber, SheetIdNumber};


pub fn get_sheets_id_numbers(path_str: &str) -> Vec<SheetIdNumber> {
    let mut result = Vec::new();
    let path = std::path::Path::new(path_str);

    let read_reader_result = panic::catch_unwind(|| {
        umya_spreadsheet::reader::xlsx::lazy_read(path).unwrap()
    });

    if let Err(err) = read_reader_result {
        error!("read reader excel[{:?}] error: {:?}",path_str, err);
        return result;
    }
    let mut book = read_reader_result.unwrap();
    book.read_sheet_collection();
    let sheets = book.get_sheet_collection_mut();
    for worksheet in sheets {
        let sheet_name = worksheet.get_name().to_string();
        let mut id_numbers: Vec<IdNumber> = Vec::new();
        let mut cells = worksheet.get_cell_collection_mut();
        cells = cells_sort(cells);
        for cell in cells {
            let coordinate = cell.get_coordinate();
            let col_num = coordinate.get_col_num();
            let row_num = coordinate.get_row_num();
            let raw_value = cell.get_raw_value();
            match raw_value {
                CellRawValue::String(value) => {
                    if is_valid_id_number(value.as_str()) {
                        id_numbers.push(IdNumber {
                            id: value.clone(),
                            col: col_num.clone(),
                            row: row_num.clone(),
                        })
                    }
                }
                CellRawValue::Str(value) => {
                    if is_valid_id_number(value.as_str()) {
                        id_numbers.push(IdNumber {
                            id: value.clone(),
                            col: col_num.clone(),
                            row: row_num.clone(),
                        })
                    }
                }
                CellRawValue::Lazy(value) => {
                    if is_valid_id_number(value.as_str()) {
                        id_numbers.push(IdNumber {
                            id: value.clone(),
                            col: col_num.clone(),
                            row: row_num.clone(),
                        })
                    }
                }
                _ => {}
            }
        }
        if id_numbers.len() > 0 {
            result.push(SheetIdNumber {
                sheet_name: sheet_name.clone(),
                id_numbers,
            })
        }
    }
    return result;
}

pub fn save_excel_file(sheet_id_numbers: Vec<SheetIdNumber>, prefix: usize, suffix: usize, symbol: &str) -> Result<String, String> {
    let config_option = get_config();
    if config_option.is_none() {
        return Err("获取配置错误".to_string());
    }
    let config = config_option.unwrap();
    let excel_path_str = get_excel_path_mem();
    if excel_path_str.len() <= 0 {
        return Err("没有选择任何文件".to_string());
    }
    let excel_path = std::path::Path::new(excel_path_str.as_str());
    let read_reader_result = panic::catch_unwind(|| {
        umya_spreadsheet::reader::xlsx::lazy_read(excel_path).unwrap()
    });

    if let Err(err) = read_reader_result {
        error!("read reader excel[{:?}] error: {:?}",excel_path_str, err);
        return Err("读取excel错误".to_string());
    }
    let mut book = read_reader_result.unwrap();
    book.read_sheet_collection();
    let sheets = book.get_sheet_collection_mut();
    for sheet_id_number in sheet_id_numbers {
        let sheet_result = book.get_sheet_by_name_mut(sheet_id_number.sheet_name.as_str());
        match sheet_result {
            Ok(sheet) => {
                let id_numbers = sheet_id_number.id_numbers;
                for id_number in id_numbers {
                    let cell_value = sheet.get_cell_value_mut((id_number.col, id_number.row));
                    // 保留id_number.id前suffix位，后suffix位，中间替换成symbol值
                    let mut id_number_str = id_number.id.clone();
                    let id_number_str_prefix = &id_number_str[..suffix];
                    let id_number_str_suffix = &id_number_str[(id_number_str.len() - suffix)..];
                    id_number_str = format!("{}{}{}", id_number_str_prefix, symbol, id_number_str_suffix);
                    cell_value.set_value_string(id_number_str);
                }
            }
            Err(err) => {
                error!("get sheet[{}] error: {:?}", sheet_id_number.sheet_name, err);
            }
        }
    }

    let save_path_option = config.save_path;
    if save_path_option.is_none() {
        return Err("无法获取保存路径".to_string());
    }
    let save_path_str = save_path_option.unwrap();
    let save_path = std::path::Path::new(save_path_str.as_str());
    let result = panic::catch_unwind(|| {
        umya_spreadsheet::writer::xlsx::write(&book, save_path).unwrap();
    });
    match result {
        Ok(_) => {
            info!("write excel[{:?}] success", save_path);
            Ok("写入excel成功".to_string())
        }
        Err(err) => {
            error!("write excel[{:?}] error: {:?}",save_path, err);
            Err("写入excel错误".to_string())
        }
    }
}


pub fn cells_sort(mut cells: Vec<&mut Cell>) -> Vec<&mut Cell> {
    cells.sort_by(|o1, o2| {
        let coordinate_one = o1.get_coordinate();
        let row_num_one = coordinate_one.get_row_num();

        let coordinate_two = o2.get_coordinate();
        let row_num_two = coordinate_two.get_row_num();

        let cmp_row_num = row_num_one.cmp(row_num_two);

        if cmp_row_num != std::cmp::Ordering::Equal {
            return cmp_row_num;
        }

        let col_num_one = coordinate_one.get_col_num();
        let col_num_two = coordinate_two.get_col_num();
        col_num_one.cmp(col_num_two)
    });
    cells
}

pub fn is_valid_id_number(id: &str) -> bool {
    let id_regex = Regex::new(r"^[1-9]\d{5}(18|19|20)\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$").unwrap();
    id_regex.is_match(id)
}